<?php
class JobSeeker
{
    public static function getDetailJobseeker($StaffID){
        $sql="SELECT *, GetNameProvinceByID(ProvinceID, PageLanguage) AS ProvinceName, GetNameProvinceByID(ExpectWorkingLocation, PageLanguage) AS ExpectLocationName
            FROM staff
            WHERE StaffID = :StaffID";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $command->bindParam(':StaffID', $StaffID);
        return $command->queryRow();
    }
    
    public static function editStatusJobSeeker($StaffID, $Status){
        $sql="UPDATE staff
            SET ActiveStatus = :ActiveStatus
            WHERE StaffID = :StaffID;";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $command->bindParam(':StaffID', $StaffID);
        $command->bindParam(':ActiveStatus', $Status);
        return $command->execute();
    }

    public static function insertJobseeker($StaffName, $Gender, $Nationality, $DOB, $Address, $ProvinceID, $ZipCode, $Email, $Phone, $Mobile, $Education, $Institution, $Major, $EnSkill, $JpSkill, $WorkPosition, $WorkExperienceDetail, $ExpectWorkingPosition, $ExpectWorkingLocation, $ExpectSalary, $Note, $JobID, $PageLanguage, $ActiveStatus){
        $sql="INSERT INTO staff (StaffName, Gender, Nationality, DOB,
            Address, ProvinceID, ZipCode, Email, Phone, Mobile,
            Education, Institution, Major, EnSkill, JpSkill,
            WorkPosition, WorkExperienceDetail, ExpectWorkingPosition, ExpectWorkingLocation,
            ExpectSalary, Note, JobID, PageLanguage, ActiveStatus, CreateDate)
             Value(:StaffName, :Gender, :Nationality, :DOB,
            :Address, :ProvinceID, :ZipCode, :Email, :Phone, :Mobile,
            :Education, :Institution, :Major, :EnSkill, :JpSkill,
            :WorkPosition, :WorkExperienceDetail, :ExpectWorkingPosition,
            :ExpectWorkingLocation, :ExpectSalary, :Note, :JobID,
            :PageLanguage, :ActiveStatus, CURDATE())";
        
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $command->bindParam(':StaffName', $StaffName);
        $command->bindParam(':Gender', $Gender);
        $command->bindParam(':Nationality', $Nationality);
        $command->bindParam(':DOB', $DOB);
        $command->bindParam(':Address', $Address);
        $command->bindParam(':ProvinceID', $ProvinceID);
        $command->bindParam(':ZipCode', $ZipCode);
        $command->bindParam(':Email', $Email);
        $command->bindParam(':Phone', $Phone);
        $command->bindParam(':Mobile', $Mobile);
        $command->bindParam(':Education', $Education);
        $command->bindParam(':Institution', $Institution);
        $command->bindParam(':Major', $Major);
        $command->bindParam(':EnSkill', $EnSkill);
        $command->bindParam(':JpSkill', $JpSkill);
        $command->bindParam(':WorkPosition', $WorkPosition);
        $command->bindParam(':WorkExperienceDetail', $WorkExperienceDetail);
        $command->bindParam(':ExpectWorkingPosition', $ExpectWorkingPosition);
        $command->bindParam(':ExpectWorkingLocation', $ExpectWorkingLocation);
        $command->bindParam(':ExpectSalary', $ExpectSalary);
        $command->bindParam(':Note', $Note);
        $command->bindParam(':JobID', $JobID);
        $command->bindParam(':PageLanguage', $PageLanguage);
        $command->bindParam(':ActiveStatus', $ActiveStatus);
        return $command->execute();
    }
    
    public static function updateJobseeker($StaffID, $StaffName, $Gender, $Nationality, $DOB, $Address, $ProvinceID, $ZipCode, $Email, $Phone, $Mobile, $Education, $Institution, $Major, $EnSkill, $JpSkill, $WorkPosition, $WorkExperienceDetail, $ExpectWorkingPosition, $ExpectWorkingLocation, $ExpectSalary, $Note, $JobID, $PageLanguage){
        
        $sql="UPDATE staff
            SET StaffName = :StaffName, Gender = :Gender, Nationality = :Nationality,
                DOB = :DOB, Address = :Address, ProvinceID = :ProvinceID, ZipCode = :ZipCode,
                Email = :Email, Phone = :Phone, Mobile = :Mobile,
                Education = :Education, Institution = :Institution, Major = :Major,
                EnSkill = :EnSkill, JpSkill = :JpSkill, WorkPosition = :WorkPosition, WorkExperienceDetail = :WorkExperienceDetail,
                ExpectWorkingPosition = :ExpectWorkingPosition, ExpectWorkingLocation = :ExpectWorkingLocation,
                ExpectSalary = :ExpectSalary, Note = :Note, JobID = :JobID,
                PageLanguage = :PageLanguage, EditDate = CURDATE()
            WHERE StaffID = :StaffID;";
        
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $command->bindParam(':StaffID', $StaffID);
        $command->bindParam(':StaffName', $StaffName);
        $command->bindParam(':Gender', $Gender);
        $command->bindParam(':Nationality', $Nationality);
        $command->bindParam(':DOB', $DOB);
        $command->bindParam(':Address', $Address);
        $command->bindParam(':ProvinceID', $ProvinceID);
        $command->bindParam(':ZipCode', $ZipCode);
        $command->bindParam(':Email', $Email);
        $command->bindParam(':Phone', $Phone);
        $command->bindParam(':Mobile', $Mobile);
        $command->bindParam(':Education', $Education);
        $command->bindParam(':Institution', $Institution);
        $command->bindParam(':Major', $Major);
        $command->bindParam(':EnSkill', $EnSkill);
        $command->bindParam(':JpSkill', $JpSkill);
        $command->bindParam(':WorkPosition', $WorkPosition);
        $command->bindParam(':WorkExperienceDetail', $WorkExperienceDetail);
        $command->bindParam(':ExpectWorkingPosition', $ExpectWorkingPosition);
        $command->bindParam(':ExpectWorkingLocation', $ExpectWorkingLocation);
        $command->bindParam(':ExpectSalary', $ExpectSalary);
        $command->bindParam(':Note', $Note);
        $command->bindParam(':JobID', $JobID);
        $command->bindParam(':PageLanguage', $PageLanguage);
        return $command->execute();
    }
    
    public static function deleteJobseeker($StaffID){
        $sql="UPDATE staff
            SET DeleteDate = CURDATE()
            WHERE StaffID = :StaffID;";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $command->bindParam(':StaffID', $StaffID);
        return $command->execute();
    }
    
    public static function getJobseekerList($page, $dataPerPage, $isAdmin){
        
        $status = $isAdmin?"'P','U'":"'P'";
        $sql = "SELECT *
            FROM staff
            WHERE ActiveStatus IN ({$status}) AND DeleteDate IS NULL 
            ORDER BY CreateDate DESC, StaffID DESC";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $maxPage =  ceil($command->execute()/$dataPerPage);
        
        $startRow = $dataPerPage*($page-1);
        $sql = "SELECT *, GetNameProvinceByID(ProvinceID, PageLanguage) AS ProvinceName, GetNameProvinceByID(ExpectWorkingLocation, PageLanguage) AS ExpectLocationName 
            FROM staff
            WHERE ActiveStatus IN ({$status}) AND DeleteDate IS NULL 
            ORDER BY CreateDate DESC, StaffID DESC
            LIMIT {$startRow},{$dataPerPage}";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $data =  $command->queryAll();
        return array(
            'MaxPage' => $maxPage,
            'data' => $data
        );
        
    }
    
    public static function getJobseekerListByKeyword($Keyword, $StaffID, $Province, $page, $dataPerPage, $isAdmin){
        
        $status = $isAdmin?"'P','U'":"'P'";
        $sql = "SELECT *
            FROM staff
            WHERE ActiveStatus IN ({$status}) AND DeleteDate IS NULL
                AND ((StaffID = :StaffID OR '' = :StaffID) 
                        AND ((ProvinceID = :ProvinceID OR 0 = :ProvinceID) 
                            OR (ExpectWorkingLocation = :ProvinceID OR 0 = :ProvinceID))
                        AND (LOWER(StaffName) LIKE :Keyword
                            OR LOWER(WorkPosition) LIKE :Keyword
                            OR LOWER(WorkExperienceDetail) LIKE :Keyword
                            OR LOWER(ExpectWorkingPosition) LIKE :Keyword 
                            OR LOWER(ExpectWorkingPosition) LIKE :Keyword))
            ORDER BY CreateDate DESC, StaffID DESC";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $command->bindParam(':StaffID', $StaffID);
        $command->bindParam(':ProvinceID', $Province);
        $Keyword = '%'.strtolower($Keyword).'%';
        $command->bindParam(':Keyword', $Keyword);
        $maxPage =  ceil($command->execute()/$dataPerPage);
        
        $startRow = $dataPerPage*($page-1);
        $sql = "SELECT *, GetNameProvinceByID(ProvinceID, PageLanguage) AS ProvinceName, GetNameProvinceByID(ExpectWorkingLocation, PageLanguage) AS ExpectLocationName 
            FROM staff
            WHERE ActiveStatus IN ({$status}) AND DeleteDate IS NULL
                AND ((StaffID = :StaffID OR '' = :StaffID) 
                        AND ((ProvinceID = :ProvinceID OR 0 = :ProvinceID) 
                            OR (ExpectWorkingLocation = :ProvinceID OR 0 = :ProvinceID))
                        AND (LOWER(StaffName) LIKE :Keyword
                            OR LOWER(WorkPosition) LIKE :Keyword
                            OR LOWER(WorkExperienceDetail) LIKE :Keyword
                            OR LOWER(ExpectWorkingPosition) LIKE :Keyword 
                            OR LOWER(ExpectWorkingPosition) LIKE :Keyword))
            ORDER BY CreateDate DESC, StaffID DESC
            LIMIT {$startRow},{$dataPerPage}";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $command->bindParam(':StaffID', $StaffID);
        $command->bindParam(':ProvinceID', $Province);
        $command->bindParam(':Keyword', $Keyword);
        $data =  $command->queryAll();
        return array(
            'MaxPage' => $maxPage,
            'data' => $data
        );
        
    }

    public static function getEducationEN($EducationID){
        switch ($EducationID) {
            case 1:
                return "Others";
            case 2:
                return "High school/Vocational";
            case 3:
                return "Diploma";
            case 4:
                return "Bachelor degree";
            case 5:
                return "Post-Graduate";
            default:
                return $EducationID;
        }
    }
    
    public static function getEducationTH($EducationID){
        switch ($EducationID) {
            case 1:
                return "อื่นๆ";
            case 2:
                return "มัธยมศึกษาตอนปลาย/ปวช.";
            case 3:
                return "ปวส.";
            case 4:
                return "ปริญญาตรี";
            case 5:
                return "สูงกว่าปริญญาตรี";
            default:
                return $EducationID;
        }
    }
    
    public static function getLanguageSkillEN($LangSkillID){
        switch ($LangSkillID) {
            case 1:
                return "Impossible";
            case 2:
                return "Weak";
            case 3:
                return "Daily conversation level";
            case 4:
                return "Business conversation level";
            case 5:
                return "Native";
            default:
                return $LangSkillID;
        }
    }
    
    public static function getLanguageSkillTH($LangSkillID){
        switch ($LangSkillID) {
            case 1:
                return "ไม่สามารถทำได้";
            case 2:
                return "ไม่ถนัด";
            case 3:
                return "ระดับสนทนาทั่วไป";
            case 4:
                return "ระดับสนทนาทางธุรกิจ";
            case 5:
                return "เจ้าของภาษา";
            default:
                return $LangSkillID;
        }
    }
}
?>
